package org.wevil.util;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.listener.PageReadListener;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import lombok.SneakyThrows;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;
import org.wevil.anno.Excel;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.sql.*;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.regex.Pattern;

/**
 * Excel操作工具类
 *
 * @author wanglei
 * @since 2019/5/21
 */
public class ExcelUtil {

    /**
     * 定义自定义日期格式
     */
    private static final Map<Short, String> dateFormatType = new HashMap<>();

    private static final String[] sources = new String[]{"A", "B", "C", "D", "E",
            "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R",
            "S", "T", "U", "V", "W", "X", "Y", "Z"};
    /**
     * 正则匹配
     */
    final private static String LETTER_PATTERN = "[A-Z]+";

    static {
        dateFormatType.put((short) 14, "yyyy-MM-dd");
        dateFormatType.put((short) 31, "yyyy年MM月dd日");
        dateFormatType.put((short) 57, "yyyy年MM月");
        dateFormatType.put((short) 58, "MM月dd日");
        dateFormatType.put((short) 20, "HH:mm");
        dateFormatType.put((short) 32, "HH时:mm分");
    }

    // ============================ 读取 =======================================================//

    /**
     * 读取excel数据，默认首行为表头，默认读取Sheet1
     *
     * @param filePath 文件绝对路径
     * @param clazz    映射的class类型，需要@ExcelProperty注解指定列名或列下标
     */
    public static <T> List<T> read(String filePath, Class<T> clazz) {
        return read(filePath, clazz, "Sheet1");
    }

    /**
     * 读取excel数据，默认首行为表头
     *
     * @param filePath 文件绝对路径
     * @param clazz    映射的class类型，需要@ExcelProperty注解指定列名或列下标
     */
    public static <T> List<T> read(String filePath, Class<T> clazz, String sheetName) {
        List<T> list = new ArrayList<>();
        EasyExcel.read(filePath, clazz, new PageReadListener<T>(list::addAll))
                .sheet(sheetName)
                .doRead();
        return list;
    }

    /**
     * 读取excel数据，指定表头
     *
     * @param filePath      文件绝对路径
     * @param clazz         映射的class类型，需要@ExcelProperty注解指定列名或列下标
     * @param headRowNumber 表头在哪一行，默认第一行
     */
    public static <T> List<T> read(String filePath, Class<T> clazz, Integer headRowNumber) {
        List<T> list = new ArrayList<>();
        EasyExcel.read(filePath, clazz, new PageReadListener<T>(list::addAll))
                .sheet()
                .headRowNumber(headRowNumber)
                .doRead();
        return list;
    }

    /**
     * 获取poi的excel操作对象
     */
    public static Workbook getExcelByPath(String excelPath) {
        try {
            File excelFile = new File(excelPath);
            FileInputStream in = new FileInputStream(excelFile);
            return WorkbookFactory.create(in);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("[" + excelPath + "] 地址读取错误");
            return null;
        }
    }

    /**
     * 根据sheet名，行，列，读取值
     *
     * @param workbook   Workbook对象
     * @param sheetName  工作表名
     * @param rowNumber  行号
     * @param cellNumber 列名
     */
    public static Object readValue(Workbook workbook, String sheetName, int rowNumber, String cellNumber) {
        Sheet sheet = getSheetByPattern(workbook, sheetName);
        if (sheet != null) {
            Row row = sheet.getRow((rowNumber - 1));
            if (row != null) {
                Cell cell = row.getCell(letter2Number(cellNumber));
                return getCellValue(workbook, cell);
            }
        }
        return null;
    }

    /**
     * 获取单元格的值
     *
     * @param workbook Workbook 对象
     * @param cell     Cell 对象
     * @return 返回指定格式的
     */
    public static Object getCellValue(Workbook workbook, Cell cell) {
        Object obj = null;
        if (workbook != null && cell != null) {
            switch (cell.getCellType()) {
                case BOOLEAN:
                    obj = cell.getBooleanCellValue();
                    break;
                case ERROR:
                    obj = cell.getErrorCellValue();
                    break;
                case NUMERIC:
                    // 获取数据格式
                    short dataFormat = cell.getCellStyle().getDataFormat();
                    // 如果是自定义日期
                    if (dateFormatType.containsKey(dataFormat)) {
                        SimpleDateFormat sdf = new SimpleDateFormat(dateFormatType.get(dataFormat));
                        obj = sdf.format(cell.getDateCellValue());
                    }
                    // 如果是百分比
                    else if (dataFormat == Short.valueOf("9")) {
                        obj = NumberUtil.decimal2Percent(new BigDecimal(cell.getNumericCellValue()));
                    }
                    // 否则就按数字处理
                    else {
                        // 输入包含"E"
                        if (!String.valueOf(cell.getNumericCellValue()).contains("E")) {
                            obj = cell.getNumericCellValue();
                            if (obj.toString().contains(",")) {
                                obj = obj.toString().replace(",", "");
                            }
                            obj = NumberUtil.formatNumberNoZero(obj.toString());
                        } else {
                            obj = new DecimalFormat("#").format(cell.getNumericCellValue());
                        }
                    }
                    break;
                case STRING:
                    obj = cell.getStringCellValue();
                    break;
                case FORMULA:
                    obj = cell.getNumericCellValue();
                    try {
                        FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
                        CellValue cellValue = evaluator.evaluate(cell);
                        obj = getCellValue(cellValue);
                        // 如果是数字，去除0
                        if (obj != null && NumberUtil.isNumeric(obj.toString())) {
                            if (obj != null && NumberUtil.isNumeric(obj.toString())) {
                                obj = new DecimalFormat("#").format(cell.getNumericCellValue());
                            }
                        }
                    } catch (Exception e) {
                        // 读取字符串
                        System.out.println("读取公式出错，改为字符串读取。错误信息：" + e.getMessage());
                        try {
                            obj = cell.getStringCellValue();
                        } catch (Exception ex) {
                            System.out.println("读取单元格字符串出错：" + e.getMessage());
                        } finally {
                            String sheetName = cell.getSheet().getSheetName();
                            System.out.println("工作表：[" + sheetName + "]，行:" + cell.getRowIndex() + "，列:" + cell.getColumnIndex() + "的公式参数程序无法读取，直接返回对应的字符串内容");
                            cell.setCellType(CellType.STRING);
                            System.out.println("字符串内容：" + cell.getStringCellValue());
                        }
                    }
                    break;
                default:
                    break;
            }
        }
        return obj;
    }

    /**
     * 根据sheet名，列1和列1的值， 读取列2里和列1同行的值
     *
     * @param workbook        Workbook 对象
     * @param sheetName       工作表名
     * @param cellName1       列1的名称
     * @param cellName2       列2的名称
     * @param matchCell1Value 要匹配的列1的值
     * @return 返回匹配到的值
     */
    public static Object readCell2ValueByCell1(Workbook workbook, String sheetName, String cellName1, String
            cellName2, String matchCell1Value) {
        if (workbook != null) {
            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet != null) {
                int rowNumber = sheet.getPhysicalNumberOfRows();
                for (int j = 0; j < rowNumber; j++) {
                    Row row = sheet.getRow(j);
                    // 获取cell1的值
                    if (row != null) {
                        Cell cell1 = row.getCell(letter2Number(cellName1));
                        Object cell1Value = getCellValue(workbook, cell1);
                        if (cell1Value != null) {
                            // 如果匹配到
                            if (cell1Value.toString().contains(matchCell1Value)) {
                                // 获取另一列的值
                                Cell cell2 = row.getCell(letter2Number(cellName2));
                                return getCellValue(workbook, cell2);
                            }
                        }
                    }
                }
            }
        }
        return null;
    }

    /**
     * 根据sheet名，列名，起始行号和终止行号获取累加值，累加数字
     *
     * @param workbook      Workbook 对象
     * @param sheetName     工作表名
     * @param cellIndex     列索引
     * @param startRowIndex 起始索引
     * @param endRowIndex   终止行索引
     * @return 返回累加的值
     */
    public static BigDecimal readValueByCellSum(Workbook workbook, String sheetName, String cellIndex, Integer
            startRowIndex, Integer endRowIndex) {
        BigDecimal bigDecimal = null;
        Sheet sheet = workbook.getSheet(sheetName);
        if (sheet != null) {
            // 最大行号
            int endRowIndexMax = sheet.getPhysicalNumberOfRows();
            if (endRowIndex == null) {
                endRowIndex = endRowIndexMax;
            }
            for (int i = startRowIndex; i <= endRowIndex; i++) {
                Row row = sheet.getRow(excelNumber2POINumberRow(i));
                if (row != null) {
                    Cell cell = row.getCell(letter2Number(cellIndex));
                    if (cell != null) {
                        Object object = getCellValue(workbook, cell);
                        // 判断是否是数字
                        if (object != null && NumberUtil.isNumeric(object.toString())) {
                            BigDecimal temp = new BigDecimal(object.toString());
                            if (bigDecimal == null) {
                                bigDecimal = new BigDecimal(0);
                            }
                            bigDecimal = bigDecimal.add(temp);
                        }
                    }
                }
            }
        }
        return bigDecimal;
    }

    /**
     * 获取一个工作表的有效行
     *
     * @param workbook poi的excel对象
     * @param sheet    poi的工作表对象
     * @return 返回当前工作表的有效行索引
     */
    public static int getValidRowIndex(Workbook workbook, Sheet sheet) {
        int validRowIndex = 0;
        // 1. 先获取该工作表的物理行数
        int physicalNumberOfRows = sheet.getLastRowNum();
        // 2. 倒序检查每一行的内容
        f1:
        for (int i = physicalNumberOfRows; i >= 0; i--) {
            Row row = sheet.getRow(i);
            // 如果row不为空，则认为当前行为有效行
            if (row != null) {
                // 2.1 获取每一行的最后列数
                int lastCellNum = row.getLastCellNum();
                // 2.2 遍历检查当前行每一个单元格内容
                for (int j = 0; j <= lastCellNum; j++) {
                    Cell cell = row.getCell(j);
                    // 2.2.1. 如果当前单元格有内容，则认为当前行为有效行
                    if (ExcelUtil.cellIsNotNull(workbook, cell)) {
                        if (ExcelUtil.cellIsNotNull(workbook, cell)) {
                            validRowIndex = i;
                            break f1;
                        }
                    }
                }
            }
        }
        // 3. 读取合并单元格，如果合并单元格的最后一行比有效行大，则设置为有效行
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        if (cellRangeAddressList != null && cellRangeAddressList.size() > 0) {
            for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
                int lastRow = cellRangeAddress.getLastRow();
                if (lastRow > validRowIndex) {
                    validRowIndex = lastRow;
                }
            }
        }
        return validRowIndex;
    }

    /**
     * 获取一个工作表的有效列
     *
     * @param workbook poi的excel对象
     * @param sheet    poi的工作表对象
     * @return 返回当前工作表的有效列索引
     */
    public static int getValidColumnIndex(Workbook workbook, Sheet sheet) {
        int validColumnIndex = 0;
        // 1. 获取一个工作表的有效行
        int validRowIndex = getValidRowIndex(workbook, sheet);
        // 2. 检查读取每一行有效列
        for (int i = 0; i <= validRowIndex; i++) {
            Row row = sheet.getRow(i);
            if (row != null) {
                // 2.1 获取每一行的最后列号
                int lastCellNum = row.getLastCellNum();
                // 2.2 倒序遍历检查当前列每一个单元格内容
                for (int j = lastCellNum; j >= 0; j--) {
                    Cell cell = row.getCell(j);
                    // 2.2.1. 如果当前单元格有内容，则认为当前行为有效行
                    if (ExcelUtil.cellIsNotNull(workbook, cell)) {
                        if (j > validColumnIndex) {
                            if (ExcelUtil.cellIsNotNull(workbook, cell)) {
                                if (j > validColumnIndex) {
                                    validColumnIndex = j;
                                    break;
                                }
                            }
                        }
                    }
                }
            }
        }
        // 3. 读取合并单元格，读取每个合并单元格的有效列
        List<CellRangeAddress> cellRangeAddressList = sheet.getMergedRegions();
        if (cellRangeAddressList != null && cellRangeAddressList.size() > 0) {
            for (CellRangeAddress cellRangeAddress : cellRangeAddressList) {
                int lastColumn = cellRangeAddress.getLastColumn();
                if (lastColumn > validColumnIndex) {
                    validColumnIndex = lastColumn;
                }
            }
        }
        return validColumnIndex;
    }

    // ============================ 创建 =======================================================//

    /**
     * 创建一个空的excel
     *
     * @param pathName excel完整地址，示例：D:/测试.xlsx
     * @author wevil
     * @since 2023-11-11
     */
    @SneakyThrows
    public static void createBlankExcel(String pathName) {
        XSSFWorkbook excel_sheet = new XSSFWorkbook();

        // Create a new file at the specified path location
        File file = new File(pathName);
        if (file.exists()) {
            file.delete();
            file.createNewFile();
        }

        // Create a connection using the specified file
        FileOutputStream ostream = new FileOutputStream(file);

        // write operation on the file
        excel_sheet.write(ostream);
        // close connection
        ostream.close();
    }

    /**
     * 创建一个空的excel
     *
     * @param path excel地址，示例：D:/
     * @param name excel名称，示例：测试.xlsx
     * @author wevil
     * @since 2023-11-11
     */
    @SneakyThrows
    public static void createBlankExcel(String path, String name) {
        XSSFWorkbook excel_sheet = new XSSFWorkbook();

        // Create a new file at the specified path location
        File file = new File(path + name);

        // Create a connection using the specified file
        FileOutputStream ostream = new FileOutputStream(file);

        // write operation on the file
        excel_sheet.write(ostream);
        // close connection
        ostream.close();
    }

    // ============================ 删除 =======================================================//

    /**
     * 删除行，该行的索引,如果有合并的单元格的则不支持，会有问题
     *
     * @param sheet    a Excel sheet
     * @param rowIndex a 0 based index of removing row
     * @author wanglei
     * @since 2020/7/15
     */
    public static void removeRow(Sheet sheet, int rowIndex) {
        int lastRowNum = sheet.getLastRowNum();
        if (rowIndex >= 0 && rowIndex < lastRowNum) {
            //将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
            sheet.shiftRows(rowIndex + 1, lastRowNum, -1);
            // apache poi版本大于3.17 ，还4.1.0 ，有一个bug shiftRows使用XSSF 。 在那里，移动后，单元格中的引用保持旧状态，而不是被调整为新的行
            if (sheet instanceof XSSFSheet) {
                XSSFSheet xSSFSheet = (XSSFSheet) sheet;
                // correcting bug that shiftRows does not adjusting references of the cells
                // if row 3 is shifted down, then reference in the cells remain r="A3", r="B3", ...
                // they must be adjusted to the new row thoug: r="A4", r="B4", ...
                // apache poi 3.17 has done this properly but had have other bugs in shiftRows.
                for (int r = xSSFSheet.getFirstRowNum(); r < sheet.getLastRowNum() + 1; r++) {
                    XSSFRow row = xSSFSheet.getRow(r);
                    if (row != null) {
                        long rRef = row.getCTRow().getR();
                        for (Cell cell : row) {
                            String cRef = ((XSSFCell) cell).getCTCell().getR();
                            ((XSSFCell) cell).getCTCell().setR(cRef.replaceAll("[0-9]", "") + rRef);
                        }
                    }
                }
                // end correcting bug
            }
        }
        if (rowIndex == lastRowNum) {
            Row removingRow = sheet.getRow(rowIndex);
            if (removingRow != null)
                sheet.removeRow(removingRow);
        }
    }

    /**
     * 清空Workbook内的所有内容
     *
     * @param workbook 要清空的对象
     */
    public static void clearWorkbook(Workbook workbook) {
        if (workbook != null) {
            int sheetCount = workbook.getNumberOfSheets();
            for (int k = 0; k < sheetCount; k++) {
                workbook.removeSheetAt(0);
            }
        }
    }

    // ============================ 导出 =======================================================//

    /**
     * 导出单个sheet表格，前端下载用
     *
     * @param list     数据列表
     * @param fileName 文件名称
     */
    public static <T> void writeExcel(List<T> list, String fileName) throws IOException {
        HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
        response.setCharacterEncoding("utf8");
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName + ".xlsx", "UTF-8"));
        response.setHeader("Cache-Control", "no-store");
        response.addHeader("Cache-Control", "max-age=0");
        EasyExcel.write(response.getOutputStream(), list.get(0).getClass()).registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet(fileName)
                .doWrite(list);
    }

    /**
     * 集合数据导出excel，需提前准备好一个空的excel放在指定目录下
     *
     * @param filePath 一个空的excel路径。示例：D:/测试.xlsx
     * @param list     要导入excel的数据集合
     * @param clazz    集合里的数据类型
     */
    public static void listExportExcel(String filePath, List<?> list, Class<?> clazz) {
        try {
            // 创建一个空的excel
            createBlankExcel(filePath);
            // 获取Workbook
            Workbook workbook = ExcelUtil.getExcelByPath(filePath);

            // 清空原有数据
            clearWorkbook(workbook);
            assert workbook != null;
            Sheet sheet = workbook.createSheet(clazz.getSimpleName());

            // 反射出对象的所有字段
            Field[] fields = clazz.getDeclaredFields();
            // 在excel里创建表头
            Row row1 = sheet.createRow(0);
            for (int i = 0; i < fields.length; i++) {
                // 让Excel自动适应列宽
                //sheet.autoSizeColumn(i);

                // 获取字段注解
                Excel annotation = fields[i].getAnnotation(Excel.class);
                // 获取字段名
                String columnName = fields[i].getName();
                if (annotation != null) {
                    // 获取表列头的名字
                    columnName = annotation.name();
                    // 设置列宽
                    sheet.setColumnWidth(i, annotation.width() * 256);
                }

                // 创建行的单元格,也是从0开始
                Cell cell1 = row1.createCell(i);
                // 设置单元格内容
                cell1.setCellValue(columnName);
            }

            // 插入数据
            int rowNum = 1;
            for (Object obj : list) {
                // 每循环一次创建一行从 1 开始
                Row row = sheet.createRow(rowNum);
                for (int j = 0; j < fields.length; j++) {
                    // 让Excel自动适应列宽
                    //sheet.autoSizeColumn(j);
                    // 创建行的单元格,也是从0开始
                    Cell cell1 = row.createCell(j);
                    // 单元格内容
                    fields[j].setAccessible(true);
                    Object content = fields[j].get(obj);
                    content = content == null ? "" : content;
                    // 设置单元格内容
                    cell1.setCellValue(content.toString());
                }
                // rowNum++ 下一次循环创建的行向下
                rowNum++;
            }

            // 数据写入文件
            FileOutputStream fOut = new FileOutputStream(filePath);
            // 保存Excel文件
            workbook.write(fOut);
            //关闭文件流
            fOut.close();
        } catch (Exception e) {
            System.out.println("导出excel失败!" + e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 导出数据库表
     *
     * @param sheetName  导出的sheet名称
     * @param sqls       对应表的查询sql，例如：select * from user
     * @param outputFile 输出excel地址，该excel必须存在。例如：E:/ddd.xlsx
     * @param url        jdbc url，例如：jdbc:mysql://127.0.0.1:3306/rs_manage?characterEncoding=UTF-8&useSSL=false
     * @param user       数据库用户名
     * @param password   数据库密码
     */
    public static void exportTable(String sheetName, String sqls, String outputFile, String url, String user, String password) {
        Connection con;
        try {
            con = DriverManager.getConnection(url, user, password);
            // PreparedStatement 预编译
            PreparedStatement ps_struts = null;
            ResultSet rs_struts = null;
            try {
                //创建Excel文件(Workbook)
                Workbook workbook = ExcelUtil.getExcelByPath(outputFile);
                assert workbook != null;
                int sheetCount = workbook.getNumberOfSheets();
                for (int k = 0; k < sheetCount; k++) {
                    workbook.removeSheetAt(0);
                }
                String[] tempo;
                int rowNum = 1;
                try {
                    //执行数据库的sq语句
                    ps_struts = con.prepareStatement(sqls);
                    rs_struts = ps_struts.executeQuery();
                    //获取Connection对象所连接的数据库的元数据。元数据包括关于数据库的表
                    ResultSetMetaData rsm = rs_struts.getMetaData();
                    //创建一个名字为(tables)的表原名字默认为sheet

                    Sheet sheet = workbook.createSheet(sheetName);
                    //getRowCount 获取数据库表行的数量  getColumnCount获取列的数量
                    int columnCount = rsm.getColumnCount();
                    //把表头填好
                    try {
                        //在Excel里创建行,从0开始
                        Row row1 = sheet.createRow(0);
                        for (int i = 1; i <= columnCount; i++) {
                            //让Excel自动适应列宽
                            sheet.autoSizeColumn(i);
                            //获取表列头的名字
                            String columnName = rsm.getColumnName(i);
                            //创建行的单元格,也是从0开始
                            Cell cell1 = row1.createCell(i - 1);
                            //设置单元格内容
                            cell1.setCellValue(columnName);
                        }
                    } catch (Exception e) {
                        e.printStackTrace();
                    }

                    //循环数据库表中的每一行,来得到每一行的数据
                    while (rs_struts.next()) {
                        //每循环一次创建一行从 1 开始
                        Row row = sheet.createRow(rowNum);
                        tempo = new String[columnCount];
                        for (int i = 0; i < columnCount; i++) {
                            //创建行的单元格,从0开始
                            Cell cell = row.createCell(i);
                            //让Excel自动适应列宽
                            sheet.autoSizeColumn(i);
                            //getMetaData().getColumnName(i);字段名
                            String columnName = rs_struts.getMetaData().getColumnName(i + 1);
                            tempo[i] = rs_struts.getString(columnName);
                            //设置单元格内容
                            cell.setCellValue(tempo[i]);
                        }
                        //每次循环结束后要使数组为空，方便存储数据库表中下一行的数值
                        tempo = null;
                        //rowNum++ 下一次循环创建的行向下
                        rowNum++;
                    }
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
                FileOutputStream fOut = new FileOutputStream(outputFile);
                // 保存Excel文件
                workbook.write(fOut);
                //fOut.flush();
                fOut.close();//关闭文件流
            } catch (Exception e) {
                e.printStackTrace();
            }
        } catch (SQLException sqlE) {
            sqlE.printStackTrace();
        }
    }

    // ============================ 编辑 =======================================================//

    /**
     * 锁定工作表， 同时指定某列不需要解锁
     *
     * @param filePath   文件路径，距离：C:\\xxx.xls
     * @param sheetName  工作表名
     * @param columnName 不需要锁定的列名，比如：C
     * @param password   锁定密码
     */
    public static void lockSheet(String filePath, String sheetName, String columnName, String password) {
        try {
            // 读取目标excel
            Workbook workbook = ExcelUtil.getExcelByPath(filePath);
            // 设置某列锁表
            if (workbook != null) {
                Sheet sheet = workbook.getSheet(sheetName);
                if (sheet != null) {
                    sheet.protectSheet(password);
                    int physicalNumberOfRows = sheet.getPhysicalNumberOfRows();
                    for (int i = 0; i <= physicalNumberOfRows; i++) {
                        Row row = sheet.getRow(i);
                        if (row != null) {
                            Cell cell = row.getCell(ExcelUtil.letter2Number(columnName));
                            if (cell != null) {
                                CellStyle cellStyle = cell.getCellStyle();
                                cellStyle.setLocked(false);
                                cell.setCellStyle(cellStyle);
                            }
                        }
                    }
                }
            }
            // 重新写入excel
            FileOutputStream outStream = new FileOutputStream(filePath);
            assert workbook != null;
            workbook.write(outStream);
            // 关闭流
            outStream.close();
            workbook.close();
        } catch (IOException e) {
            e.printStackTrace();
            System.out.println("替换文件失败：[" + filePath + "]");
            System.out.println(e.getMessage());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    // ============================ 其他 =======================================================//

    /**
     * 判断单元格获取的值的对象是否为空
     *
     * @param workbook poi excel对象
     * @param cell     poi 单元格对象
     * @return true： 不为空，false：为空
     */
    public static boolean cellIsNotNull(Workbook workbook, Cell cell) {
        if (cell != null) {
            Object value = getCellValue(workbook, cell);
            return value != null && !value.toString().trim().equals("");
        }
        return false;
    }

    /**
     * 将以字母表示的Excel列的字母转换成数字表示
     */
    public static int letter2Number(String letter) {
        // 检查字符串是否为空
        if (letter == null || letter.isEmpty()) {
            return -1;
        }
        // 转为大写字符串
        String upperLetter = letter.toUpperCase();
        // 检查是否符合，不能包含非字母字符
        if (!upperLetter.matches(LETTER_PATTERN)) {
            return -1;
        }
        // 存放结果数值
        long num = 0;
        long base = 1;
        // 从字符串尾部开始向头部转换
        for (int i = upperLetter.length() - 1; i >= 0; i--) {
            char ch = upperLetter.charAt(i);
            num += (ch - 'A' + 1) * base;
            base *= 26;
            // 防止内存溢出
            if (num > Integer.MAX_VALUE) {
                return -1;
            }
        }
        return (int) num - 1;
    }

    /**
     * 返回该列号对应的字母
     *
     * @param columnIndex 列索引
     * @return 索引对应的列名
     */
    public static String number2Letter(int columnIndex) {
        // 字母数量
        int alphaCount = 26;
        int columnNo = columnIndex + 1;
        if (columnNo < 1) {
            throw new IllegalArgumentException();
        }
        StringBuilder sb = new StringBuilder(5);
        int remainder = columnNo % alphaCount;
        if (remainder == 0) {
            sb.append("Z");
            remainder = alphaCount;
        } else {
            sb.append(sources[remainder - 1]);
        }
        while ((columnNo = (columnNo - remainder) / alphaCount - 1) > -1) {
            remainder = columnNo % alphaCount;
            sb.append(sources[remainder]);
        }
        return sb.reverse().toString();
    }

    // ============================ 私有类 =======================================================//

    /**
     * 根据正则匹配对应的sheet
     *
     * @param workbook       工作表
     * @param sheetNameRegex 要匹配的工作表正则表达式
     */
    private static Sheet getSheetByPattern(Workbook workbook, String sheetNameRegex) {
        // 遍历去匹配对应的sheet
        for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
            Sheet sheet = workbook.getSheetAt(i);
            // 如果匹配到指定的sheet，返回指定的值
            if (Pattern.matches(sheetNameRegex, sheet.getSheetName())) {
                return sheet;
            }
        }
        return null;
    }

    /**
     * excel里的行号转为poi里读取的行号
     */
    private static int excelNumber2POINumberRow(int rowIndex) {
        if (rowIndex > 0) {
            return rowIndex - 1;
        } else {
            System.out.println("传进来的行号[] 不大于0，有误");
            return 0;
        }
    }

    /**
     * 公式取值辅助类
     *
     * @param cellValue
     */
    private static Object getCellValue(CellValue cellValue) {
        switch (cellValue.getCellType()) {
            case NUMERIC:
                return cellValue.getNumberValue();
            case STRING:
                return cellValue.getStringValue();
            case BOOLEAN:
                return cellValue.getBooleanValue();
            case ERROR:
                return cellValue.getErrorValue();
            default:
                return null;
        }
    }

    /**
     * 替换XLS的表格
     *
     * @param replaceArrayKey 需要替换的内容数组
     * @param workbook        poi workbook对象
     */
    private static void replaceExcelTable(String[] replaceArrayKey, Workbook workbook) {
        if (workbook != null) {
            Iterator<Sheet> sheetIterator = workbook.sheetIterator();
            while (sheetIterator.hasNext()) {
                Sheet sheet = sheetIterator.next();
                int rows = sheet.getPhysicalNumberOfRows();
                for (int i = 0; i < rows; i++) {
                    Row row = sheet.getRow(i);
                    if (row != null) {
                        int columns = row.getPhysicalNumberOfCells();
                        for (int j = 0; j < columns; j++) {
                            Cell cell = row.getCell(j);
                            if (cell != null) {
                                for (String s : replaceArrayKey) {
                                    if (cell.toString().contains(s)) {
                                        String value = cell.toString().replace(s, "");
                                        cell.setCellValue(value);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
}